# coding=utf-8
# sqlserver的连接
import time  # 时间函数库
from datetime import datetime

import pymssql

'''
ms = MSSQL(host='172.16.0.89', user="kent", pwd="kent@123", db="MeteringJASON") 
reclist="insert into kentgprs485(yb_nuber,SS,LJ1,LJ2,LCBJ,FXLLBJ,KGBJ,LLSXBJ,LLXXBJ,LSXX,LSBFB,DJZK,YBKJ) VALUES('" + yb_nuber + "','" + str(
    strss) + "','" + str(zxlj) + "','" + str(fxlj) + "','" + str(strdlc) + "','" + str(strfx) + "','" + str(
    strkg) + "','" + str(strllsx) + "','" + str(strllxx) + "','" + str(llxx) + "','" + str(llbfb) + "','" + str(
    djzk) + "','" + str(ybkj) + "')"

ms.ExecNonQuery(reclist)
'''
class MSSQL:
    def __init__(self, host, user, pwd, db):
        self.host = host
        self.user = user
        self.pwd = pwd
        self.db = db


    def __GetConnect(self):
        """
        得到连接信息
        返回: conn.cursor()
        """
        if not self.db:
            raise (NameError, "没有设置数据库信息")
        self.conn = pymssql.connect(host=self.host,user=self.user, password=self.pwd, database=self.db, charset="UTF-8")
        cur = self.conn.cursor()

        if not cur:
            raise (NameError, "连接数据库失败")
        else:
            return cur

    def ExecQuery(self, sql):
        """
        执行查询语句
        返回的是一个包含tuple的list，list的元素是记录行，tuple的元素是每行记录的字段

        """
        cur = self.__GetConnect()
        cur.execute(sql)
        resList = cur.fetchall()

        # 查询完毕后必须关闭连接
        self.conn.close()
        return resList

    def ExecNonQuery(self, sql):
        """
        执行非查询语句

        调用示例：
            cur = self.__GetConnect()
            cur.execute(sql)
            self.conn.commit()
            self.conn.close()
        """
        cur = self.__GetConnect()
        cur.execute(sql)
        self.conn.commit()
        self.conn.close()

def main():
    # 89数据库
    sqlServer = {
        'host':'172.16.0.89',
        'user':'kent',
        'pwd':'kent@123',
        'db':'MeteringJASON'
    }
    
    # 91备份参数数据库
    sqlServer = {
        'host':'172.16.0.92',
        'user':'sa',
        'pwd':'1234Qwer',
        'db':'KENT2'
    }
        
    try:
        sqlGprsid = ''
        gprsfile = './document/gprsid.txt'
        # gprsfile = './document/gprsid-13.02.txt'
        with open(gprsfile, 'r') as fp:
            filedata = fp.readlines()
            for line in filedata:
                line = line.strip('\n')
                sqlGprsid += "'" + line + "',"
        sqlGprsid = sqlGprsid[:-1]
        # print(sqlGprsid)

        ms = MSSQL(sqlServer['host'], sqlServer['user'], sqlServer['pwd'], sqlServer['db'])
        localTime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
        print('{},connect success!'.format(localTime))
        
        # FROM:后面是具体的表的名称：DeviceCommand
        sql = "SELECT GPRSID,CommandContent,\
            ResponseTime as 返回结果时间,\
            Result as 是否下发,\
            PresetResponse as 预置响应内容\
            FROM DeviceCommand WHERE GPRSID in  ({})".format(sqlGprsid)
        # print(sql)
        resList = ms.ExecQuery(sql)

        date_string = "2023-12-29 08:00:00"
        date_format = "%Y-%m-%d %H:%M:%S"

        # 将字符串转换为datetime对象
        date_object = datetime.strptime(date_string, date_format)
        
        result = ''
        for list in resList:
            # print(list)
            if list[-1] == '01' and list[-3] > date_object:                
                # resultDate = list[-3]
                # print(list)
                result += str(list[0]) + ',' + str(list[2]) + ',' + '成功' + '\r\n'
        print(result)
        with open('./document/result.csv','w') as fp:
            fp.write(result)
    except Exception as e:
        print('connect error!')
        
if __name__ == '__main__':
    main()

'''
# 线程池插入数据库代码
# 以下是使用ThreadPoolExecutor的修改示例：
# python
import socket
import concurrent.futures
import sqlite3

def insert_into_database(data):
    conn = sqlite3.connect("your_database.db")
    c = conn.cursor()
    # Perform database insertion operation here
    c.execute("INSERT INTO your_table (data) VALUES (?)", (data,))
    conn.commit()
    conn.close()

def handle_datagram(sock):
    with concurrent.futures.ThreadPoolExecutor() as executor:
        while True:
            data, addr = sock.recvfrom(1024)
            # Submit a new task to thread pool for each datagram received
            executor.submit(insert_into_database, data.decode())

def main():
    # Create a UDP socket
    sock = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
    server_address = ("localhost", 9999)
    sock.bind(server_address)

    # Start a separate thread to handle incoming datagrams
    thread = threading.Thread(target=handle_datagram, args=(sock,))
    thread.start()

    # Main thread can continue to do other tasks
    while True:
        # Perform other operations if required
        pass

if __name__ == "__main__":
    main()
# 在这个修改后的示例中，handle_datagram函数使用ThreadPoolExecutor来创建线程池，
# 并在线程池中提交数据库插入任务，以实现并发的插入操作。线程池将自动管理线程的创建和销毁。
'''
